package com.data.reports.manager.database;


import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据源配置实现
 * @author Hurricane.Hu
 * @version V1.0
 * @Title: JdbcDataSourceConfigService.java
 * @Package com.data.com.data.reports.manager
 * @Description
 * @date 2018 03-12 16:38.
 */
public class JdbcDataSourceConfigService implements DataSourceConfigService {

    private final static Logger LOGGER= LoggerFactory.getLogger(JdbcDataSourceConfigService.class);

    private static String TABLE_NAME ="SQL_DB_CONFIG";

    private static final String ADD_CONFIG="INSERT INTO "+ TABLE_NAME +" (config_code,config_name,url,username,password,status,create_user,create_time)" +
            " VALUES( ?, ? , ? , ?, ?, ?, ?, ? ,ROUND(UNIX_TIMESTAMP(NOW(4))*1000))";

    private static final String DELETE_CONFIG="DELETE FROM "+ TABLE_NAME +" WHERE id=?";

    private static final String GET_BY_ID ="SELECT * FROM "+ TABLE_NAME + " WHERE id=?";

    private static final String GET_BY_CODE ="SELECT * FROM "+ TABLE_NAME + " WHERE config_code=?";

    private static final String CREATE_TABLE ="CREATE TABLE IF NOT EXISTS "+ TABLE_NAME +" (" +
            "`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',"+
            "`config_code` varchar(100) DEFAULT NULL COMMENT '唯一编码'," +
            "`config_name` varchar(100) DEFAULT NULL COMMENT '数据源名称'," +
            "`url` varchar(255) DEFAULT NULL COMMENT '数据源url'," +
            "`username` varchar(100) DEFAULT NULL COMMENT '用户名'," +
            "`password` varchar(80) DEFAULT NULL COMMENT '密码'," +
            "`status` int(2) DEFAULT 0 COMMENT '状态'," +
            "`create_user` varchar(80) DEFAULT NULL COMMENT '创建人'," +
            "`create_time` bigint(20) DEFAULT NULL COMMENT '创建时间'," +
            "`modify_user` varchar(80) DEFAULT NULL COMMENT '更新人'," +
            "`modify_time` bigint(20) DEFAULT NULL COMMENT '更新时间'," +
            "PRIMARY KEY (`id`)" +
            ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据源配置'";

    private static final String CHECK_EXISTS ="SHOW TABLES LIKE '"+ TABLE_NAME +"'";

    private static volatile boolean isTableExists;


    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    private static String getUpdateSqlAndArgs(DataSourceConfig config,List<Object> args){
        if (config.getId() == null) {
            throw new IllegalArgumentException("id is null.");
        }
        StringBuilder sql = new StringBuilder("UPDATE "
                + TABLE_NAME
                + " SET modify_time=ROUND(UNIX_TIMESTAMP(now(4))*1000)");
        if (StringUtils.isNotBlank(config.getConfigCode())) {
            sql.append(",config_code=?");
            args.add(config.getConfigCode());
        }
        if (StringUtils.isNotBlank(config.getConfigName())) {
            sql.append(",config_name=?");
            args.add(config.getConfigName());
        }
        if (StringUtils.isNotBlank(config.getUrl())) {
            sql.append(",url=?");
            args.add(config.getUrl());
        }
        if (StringUtils.isNotBlank(config.getUsername())) {
            sql.append(",username=?");
            args.add(config.getUsername());
        }
        if (StringUtils.isNotBlank(config.getPassword())) {
            sql.append(",password=?");
            args.add(config.getPassword());
        }
        if (config.getStatus() != null) {
            sql.append(",status=?");
            args.add(config.getStatus());
        }
        sql.append(" WHERE id=?");
        args.add(config.getId());
        return sql.toString();
    }

    private static String getQueryPageSqlAndArgs(Map<String,String> parameters,List<Object> args){
        StringBuilder sql = new StringBuilder("SELECT * FROM " + TABLE_NAME +" WHERE 1=1 ");
        if (parameters==null || parameters.size() <=0 || args==null){
            return sql.toString();
        }
        if (StringUtils.isNotBlank(parameters.get("configCode"))) {
            sql.append(" AND config_code=? ");
            args.add(parameters.get("configCode"));
        }
        if (StringUtils.isNotBlank(parameters.get("configName"))) {
            sql.append(" AND config_name=? ");
            args.add(parameters.get("configName"));
        }
        if (StringUtils.isNotBlank(parameters.get("status"))) {
            sql.append(" AND status=? ");
            args.add(parameters.get("status"));
        }
        sql.append(" LIMIT ?,? ");
        int currentPage=StringUtils.isBlank(parameters.get("page"))? 1: Integer.parseInt(parameters.get("page"));
        int pageSize=StringUtils.isBlank(parameters.get("perPageCount"))? 15: Integer.parseInt(parameters.get("perPageCount"));
        int start=(currentPage-1) * pageSize;
        args.add(start);
        args.add(pageSize);
        return sql.toString();
    }

    private void createTableNX(){

        if(isTableExists){
            return;
        }
        isTableExists=jdbcTemplate.query(CHECK_EXISTS, new ResultSetExtractor<Boolean>() {
            public Boolean extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                return resultSet.next();
            }
        });
        if(!isTableExists){
            if(LOGGER.isDebugEnabled()){
                LOGGER.debug("创建报表配置表成功.");
            }
            jdbcTemplate.execute(CREATE_TABLE);
        }
    }

    public void init(){//初始化建表，如果表不存在
        createTableNX();
    }

    @Override
    public Integer addDataSourceConfig(DataSourceConfig config) {

        return null;
    }

    @Override
    public DataSourceConfig getDataSourceConfigById(Integer id) {

        return null;
    }

    @Override
    public DataSourceConfig getDataSourceConfigByCode(final String code) {

        return jdbcTemplate.query(GET_BY_CODE, new PreparedStatementSetter() {

            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, code);
            }
        }, new ResultSetExtractor<DataSourceConfig>() {
            public DataSourceConfig extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                if (resultSet.next()) {
                    DataSourceConfig dataSourceConfig = new DataSourceConfig();
                    dataSourceConfig.setConfigCode(resultSet.getString("config_code"));
                    dataSourceConfig.setConfigName(resultSet.getString("config_name"));
                    dataSourceConfig.setUrl(resultSet.getString("url"));
                    dataSourceConfig.setUsername(resultSet.getString("username"));
                    dataSourceConfig.setPassword(resultSet.getString("password"));
                    dataSourceConfig.setStatus(resultSet.getInt("status"));
                    return dataSourceConfig;
                }
                return null;
            }
        });
    }

    @Override
    public Integer updateDataSourceConfig(DataSourceConfig config) {

        return null;
    }

    @Override
    public Integer deleteDataSourceConfig(Integer id) {

        return null;
    }

    @Override
    public List<DataSourceConfig> listDataSourcePage(Map<String, String> parameters) {

        createTableNX();
        List<Object> args=new ArrayList<>();
        String sql=getQueryPageSqlAndArgs(parameters,args);
        return jdbcTemplate.query(sql, new RowMapper<DataSourceConfig>() {
            @Override
            public DataSourceConfig mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                DataSourceConfig dataSourceConfig = new DataSourceConfig();
                dataSourceConfig.setId(resultSet.getInt("id"));
                dataSourceConfig.setConfigCode(resultSet.getString("config_code"));
                dataSourceConfig.setConfigName(resultSet.getString("config_name"));
                dataSourceConfig.setUrl(resultSet.getString("url"));
                dataSourceConfig.setUsername(resultSet.getString("username"));
                dataSourceConfig.setPassword(resultSet.getString("password"));
                dataSourceConfig.setStatus(resultSet.getInt("status"));
                dataSourceConfig.setCreateUser(resultSet.getString("create_user"));
                dataSourceConfig.setCreateTime(resultSet.getLong("create_time"));
                dataSourceConfig.setModifyUser(resultSet.getString("modify_user"));
                dataSourceConfig.setModifyTime(resultSet.getLong("modify_time"));
                return dataSourceConfig;
            }
        }, args.toArray());
    }

    @Override
    public Map<String, String> getDataSourceMap() {

        createTableNX();
        String sql=getQueryPageSqlAndArgs(null,new ArrayList<>());
        final Map<String,String> datasourceMap=new HashMap<>();
        jdbcTemplate.query(sql, new RowMapper<DataSourceConfig>() {
            @Override
            public DataSourceConfig mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                datasourceMap.put(resultSet.getString("config_code"),resultSet.getString("config_name"));
                return null;
            }
        });

        return datasourceMap;
    }
}
